package com.cci.kangdao.dao;

import com.cci.kangdao.dao.model.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.mybatis.spring.annotation.MapperScan;

import java.util.List;
import java.util.Map;
import java.util.Set;

@MapperScan
public interface ServiceStationDao {

    //获取dbu渠道下授权服务站id和授权级别   2.8.0需求使用
    @Select("select ServiceStationID,AuthorizationLevel from SSRepair_T where Flag = 0 and CompanyID = #{companyId}")
    List<Map<String, Object>> getDBUSsrepairList(@Param("companyId") long companyId);

	//通过服务站id和服务渠道id获取授权渠道表中一条数据
	@Select("select * from SSRepair_T where ServiceStationID = #{stationId} and CompanyID = #{companyId} and Flag = 0")
	Map<String,Object> getSSRepairByStationIdAndCompanyId(@Param("stationId")long stationId,@Param("companyId")long companyId);

	//通过服务站id和服务渠道id获取服务站名称和服务站编号中一条数据
	@Select("select top 1 a.StationName,b.BusinessNo from ServiceStation_T a\n" +
			"left join SSRepair_T b on a.ID = b.ServiceStationID\n" +
			"where b.Flag = 0 and a.ID = #{stationId} and b.CompanyID = #{companyId}")
	Map<String,Object> getStationNoAndNameByStationIdAndCompanyId(@Param("stationId")long stationId,@Param("companyId")long companyId);

	//获取服务站信息表中所有经纬度为空的数据
	@Select("select * from ServiceStation_T a where a.PositionLat is null or a.PositionLon is null or a.PositionLat = '' or a.PositionLon=''")
	List<Map<String,Object>> getNullPositionLatPositionLonServiceStationList();


	//根据服务站id获取服务站信息
	@Select("select top 1 * from ServiceStation_T where ID = #{serviceStationId}")
	Map<String,Object> getServiceStationById(long serviceStationId);
	
	/**
	 * 查询字典项所有数据
	 * @param type
	 * @return
	 */
	public List<DictionariesT> findDictionariesList(String type);
	
	/**
	 * 查询用户所属企业的机型数据
	 * @param companyID
	 * @return
	 */
	public List<EngineTypeT> findEngineTypeList(String companyID);
	
	/**
	 * 根据用户所属服务站查询的机型数据
	 * @param serviceStationID
	 * @return
	 */
	public List<EngineTypeT> findEngineTypeListByStation(String serviceStationID);
	
	/**
	 *查询所有服务站信息 
	 * @param map
	 * @return
	 */
	public List<ServiceStationT> findServiceStation(Map<String, Object> map);
	
	/**
	 * 根据服务站id查询服务站级别SSLevel
	 * @param serviceStationID
	 * @return
	 */
	public List<SSRepairT> findSSRepair(String serviceStationID);
	
	/**
	 * 根据服务站id查询服务站维修机型
	 * @param serviceStationID
	 * @return
	 */
	public List<EngineTypeT> findEngineType(String serviceStationID);

	//findEngineType关联不出dbu的机型，表关联关系不对，这个sql能够关联出dbu的机型，两个sql去重  展示在服务网点
	@Select("select distinct c.EnginTypeSys,c.TypeName,c.Displacement from SSHasSMN_T a\n" +
			"inner join SMN_T b on a.ServiceModelNameId = b.ID\n" +
			"inner join EngineType_T c on b.EngineTypeID = c.ID\n" +
			"where a.ServiceStationId = #{serviceStationID} and a.Flag = 0  and b.Flag = 0 and c.Flag = 0")
	public List<EngineTypeT> findEngineTypeByDBU(@Param("serviceStationID") String serviceStationID);

	/**
	 * 根据服务站Id查询服务站图片
	 * @param serviceStationID
	 * @return
	 */
	public List<ServiceStationImgT> findServiceStationImg(String serviceStationID);
	
	/**
	 * 根据服务站Id查询服务站 
	 * @param serviceStationID
	 * @return
	 */
	public ServiceStationT findServiceStationByID(String serviceStationID);
	
	/**
	 * 根据id修改服务站数据
	 * @param map
	 * @return
	 */
	public int updateServiceStation(Map<String, Object> map);
	
	/**
	 * 新增服务站的图片信息
	 * @param bean
	 * @return
	 */
	public int insertServiceStationImg(Map<String, Object> map);
	
	/**
	 * 删除服务站的图片信息
	 * @param bean
	 * @return
	 */
	public int updateServiceStationImg(Map<String, Object> map);
	
	/**
	 * 根据服务站Id查询服务站用户信息
	 * @param serviceStationId
	 * @return
	 */
	public List<UserT> findServiceStationUserByID(Map<String, Object> map);
	
	/**
	 * 删除服务站用户信息 
	 * @param bean
	 * @return
	 */
	public int updateServiceStationUser(Map<String, Object> map);
	
	/**
	 * 获取OEM/SSOEM数据
	 * @return
	 */
	List<Map<String, Object>> getSSHasCompany();
	
	/**
	 *查询所有服务站信息 
	 * @param map
	 * @return
	 */
	public List<ServiceStationT> getStations(Map<String, Object> map);
	
	/**
	 * 添加移除记录
	 * @param map
	 * @return
	 */
	int addScanRecord(Map<String, Object> map);
	
	/**
	 * 查询服务站经纬度
	 * @param map
	 * @return
	 */
	@Select("SELECT ss.PositionLon, ss.PositionLat,respair.BusinessNo FROM ServiceStation_T ss\r\n" +
			"INNER JOIN SSRepair_T respair\r\n" + 
			"ON ss.ID = respair.ServiceStationID\r\n" + 
			"INNER JOIN Company_T company\r\n" + 
			"ON respair.CompanyID = company.ID\r\n" + 
			"WHERE company.CompanyNo = #{CompanyNo}")
	public List<Map<String,Object>> getStationLatAndLons(Map<String, Object> map);
	
	/**
	 * 查询服务站
	 * @param map
	 * @return
	 */
	@Select("SELECT * FROM ServiceStation_T ss\r\n" + 
			"INNER JOIN SSRepair_T repair\r\n" + 
			"ON ss.ID = repair.ServiceStationID\r\n" + 
			"INNER JOIN Company_T company\r\n" + 
			"ON repair.CompanyID = company.ID\r\n" + 
			"WHERE company.CompanyNo = #{CompanyNo} AND repair.BusinessNo = #{BusinessNo}")
	public List<ServiceStationT> getServiceStationByCompanyNoAndBusinessNo(Map<String,Object> map);
	
	/**
	 * 获取发动机维修表数据
	 * @Title: getSSRepairByCompanyIDAndBusinessNo  
	 * @Description: TODO(这里用一句话描述这个方法的作用)  
	 * @param @param map
	 * @param @return
	 * @return SSRepairT   
	 * @throws
	 */
	@Select("SELECT * FROM SSRepair_T WHERE CompanyID = #{CompanyID} AND BusinessNo = #{stationNo} AND Flag = 0")
	public List<SSRepairT> getSSRepairByCompanyIDAndBusinessNo(Map<String,Object> map);

    @Select("SELECT ID, ServiceStationID, CompanyID, BusinessNo, ShowAbb, AuthChannel, SSType, SSLevel, SSAuth, SSStar, AuthTime, isOnduty, HotlineTel, Tel, Remark, Flag, Systime, DelearCatetory, ServiceScope, SuperiorDepartment, AuthorizationLevel FROM SSRepair_T WHERE CompanyID = #{CompanyID}  AND Flag = 0")
     List<SSRepairT> getSSRepairBusinessNoByCompanyID(Long CompanyID);
	
	/**
	 * 获取发动机维修表数据
	 * @Title: getSSRepairByCompanyIDAndBusinessNo  
	 * @Description: TODO(这里用一句话描述这个方法的作用)  
	 * @param @param map
	 * @param @return
	 * @return SSRepairT   
	 * @throws
	 */
	@Select("SELECT * FROM SSRepair_T ss\r\n" + 
			"INNER JOIN Company_T company ON ss.CompanyID = company.ID\r\n" + 
			"WHERE company.CompanyNo = #{CompanyNo} AND ss.BusinessNo = #{BusinessNo} AND company.Flag = 0 AND ss.Flag = 0")
	public List<SSRepairT> getSSRepairByCompanyNoAndBusinessNo(Map<String,Object> map);
	
	/**
	 * 获取作废发动机维修表数据
	 * @Title: getUnActiveSSRepairByCompanyNoAndBusinessNo  
	 * @Description: TODO(这里用一句话描述这个方法的作用)  
	 * @param @param map
	 * @param @return
	 * @return SSRepairT   
	 * @throws
	 */
	@Select("SELECT * FROM SSRepair_T ss\r\n" + 
			"INNER JOIN Company_T company ON ss.CompanyID = company.ID\r\n" + 
			"WHERE company.CompanyNo = #{CompanyNo} AND ss.BusinessNo = #{BusinessNo} AND company.Flag = 0")
	public List<SSRepairT> getUnActiveSSRepairByCompanyNoAndBusinessNo(Map<String,Object> map);
	
	/**
	 * 根据服务站id获取维修表列表
	 * @Title: getSSRepairListByServiceStaionId  
	 * @Description: TODO(这里用一句话描述这个方法的作用)  
	 * @param @param map
	 * @param @return
	 * @return List<SSRepairT>   
	 * @throws
	 */
	@Select("SELECT * FROM SSRepair_T WHERE ServiceStationID = #{serviceStationId} AND Flag = 0")
	public List<SSRepairT> getSSRepairListByServiceStaionId(Map<String,Object> map);

	@Select("SELECT * FROM SSRepair_T WHERE ServiceStationID = #{serviceStationId} AND CompanyID =#{companyID} AND Flag = 0")
	public List<SSRepairT> getSSRepairListByServiceStaionIdForDCEC(Map<String,Object> map);
	
	/**
	 * 删除此服务站
	 * @Title: deleteServiceStationByCompanyIDAndBusinessNo  
	 * @Description: TODO(这里用一句话描述这个方法的作用)  
	 * @param @param map
	 * @param @return
	 * @return int   
	 * @throws
	 */
	@Update("UPDATE ServiceStation_T SET Flag = -1 WHERE ID = #{ID} AND Flag = 0")
	public int deleteServiceStationByServiceStationId(long ID);
	
	/**
	 * 更新服务站为活跃服务站
	 * @Title: updateServiceStationFlagByServiceStationId  
	 * @Description: TODO(这里用一句话描述这个方法的作用)  
	 * @param @param ID
	 * @param @return
	 * @return int   
	 * @throws
	 */
	@Update("UPDATE ServiceStation_T SET Flag = 0 WHERE ID = #{ID}")
	public int updateServiceStationFlagByServiceStationId(long ID);
	
	/**
	 * 删除服务站维修表
	 * @Title: deleteSSRepairByServiceStationId  
	 * @Description: TODO(这里用一句话描述这个方法的作用)  
	 * @param @param map
	 * @param @return
	 * @return int   
	 * @throws
	 */
	@Update("UPDATE SSRepair_T SET Flag = -1 WHERE ID = #{ID} AND Flag = 0")
	public int deleteSSRepairBySSRepairId(long ID);
	
	/**
	 * 更新服务站维修表为活跃表
	 * @Title: updateSSRepairFlagBySSRepairId  
	 * @Description: TODO(这里用一句话描述这个方法的作用)  
	 * @param @param ID
	 * @param @return
	 * @return int   
	 * @throws
	 */
	@Update("UPDATE SSRepair_T SET Flag = 0 WHERE ID = #{ID}")
	public int updateSSRepairFlagBySSRepairId(long ID);

	/**
	 * 通过服务站名称获取服务站
	 * @param stationName 服务站名称
	 * @return 服务站实体
	 */
	@Select("select * from ServiceStation_T where Flag = 0 and StationName = #{stationName}")
	ServiceStationT getServiceStationByStationName(String stationName);

	/**
	 * 根据服务站名称模糊查询服务站列表
	 * @param serviceStationName 服务商名称
	 * @return 服务商列表
	 */
	@Select("select * from ServiceStation_T where StationName = #{serviceStationName} and Flag = 0")
	List<ServiceStationT> getServiceStationListByStationName(String serviceStationName);

	@Select("select * from ServiceStation_T where StationName = #{serviceStationName} and RelationType = 2 and Flag = 0")
	List<ServiceStationT> getServiceStationListByStationNameTwo(String serviceStationName);

	/**
	 * 根据服务站名称分类型模糊查询服务站列表（top 10）
	 * @param serviceStationName 服务商名称
	 * @param type 类型 1-普通服务商，2-分公司（CHS,区域BU等）
	 * @return 服务商列表
	 */
	List<ServiceStationT> getCategoryServiceStationListLikeName(@Param("serviceStationName") String serviceStationName,@Param("type") Integer type);

	@Select("select top 1 * from ServiceStation_T ss\n" +
			"inner join SSRepair_T ssr on ssr.ServiceStationID = ss.ID\n" +
			"where ssr.Flag = 0 and ssr.CompanyID = #{companyId} and ssr.BusinessNo = #{businessNo}")
	ServiceStationT getServiceStationByStationNo(@Param("companyId") Long companyId,@Param("businessNo") String stationNo);

	@Select("select top 1 ss.* from ServiceStation_T ss\n" +
			"inner join SSRepair_T ssr on ssr.ServiceStationID = ss.ID\n" +
			"where ssr.Flag = 0 and ssr.BusinessNo = #{businessNo}")
	ServiceStationT getStationByStationNo(@Param("businessNo") String stationNo);


	@Select("select DInt as id, DValue as name from Dictionaries_T where DTableName = 'SSAuth' and Flag = 0 order by CONVERT(int, DInt)")
	public List<Map<String, Object>> getSSAuth();

	@Select("select DInt as id, DValue as name from Dictionaries_T where DTableName = 'VehicleType' and Flag = 0 order by CONVERT(int, DInt)")
	public List<Map<String, Object>> getVehicleType();

	@Select("select ID as id, TypeName as name from EngineType_T where Flag = 0 order by ID")
	public List<Map<String, Object>> getEngineType();
	
	public List<Map<String, Object>> getEngineTypeOrderByTypeName(@Param("keyword") String keyword);
	

	@Select("select * from (select distinct Abbreviation as name, COUNT(1) as id from Company_T where IsType in (4, 5) and Flag = 0 group by Abbreviation) t order by t.name collate Chinese_PRC_CI_AS")
	public List<Map<String, Object>> getOEM();

	public List<ServiceStationT> getServiceStationForCustomer(Map<String, Object> map);

	public List<ServiceStationT> getServiceStationForNonCustomer(Map<String, Object> map);

	@Select("select em.id, em.ESN from CustomerEngineRel_T cer right join EngineMsg em on cer.EngineMsgId = em.id where cer.UserId = #{userId} and cer.Flag = 0")
	public List<Map<String, Object>> getEngineList(@Param("userId") Long userId);

	@Select("select distinct a.SMN from EngineMsg a left join CustomerEngineRel_T b on a.id = b.EngineMsgId where b.Flag = 0 and b.UserId = #{userId}")
	public List<String> getSMNByUser(@Param("userId") Long userId);


	int getServiceStationCountForCustomer(Set<String> set);

	@Select("select t.Abbreviation from (select c.Abbreviation, COUNT(1) num from Company_T c where c.Abbreviation in (select distinct a.Abbreviation from Company_T a left join SSHasCompany_T b on a.ID = b.CompanyId and b.Flag = 0 where a.Flag = 0 and b.ServiceStationId = #{serviceStationID}) group by c.Abbreviation) t order by t.Abbreviation collate Chinese_PRC_CI_AS, t.num desc")
	List<String> getCompanyList(Long serviceStationID);


    //移动技师sql
    List<Map<String, Object>> getServiceStationInfoListByIds(List<Long> list);

    //移动技师sql
    List<Long> getServiceStationIdList(@Param("stationname") String stationname, @Param("stationno") String stationno);
    
    
  //根据服务站id获取服务站信息员信息id
    @Select("select distinct a.id from User_T a\n" +
			"left join UserHasRoles_T b on a.ID=b.UserID\n" +
			"left join UserGroupHasRole_T c on c.ID = b.RolesID\n" +
			"left join Roles_T d on c.RoleId = d.ID\n" +
			"where a.Flag = 0 and b.Flag = 0 and c.Flag = 0 and d.Flag = 0\n" +
			"and (d.RolesNo='R0041' or d.RolesNo='R0069') and a.ServiceStationID = #{ServiceStationID}")
  	public List<Long> getServiceStationUserIdList(@Param("ServiceStationID") long ServiceStationID);
    
    
    List<Map<String, Object>> getSubstationList(@Param("keywords") String keywords);

    ServiceStationT getSSById(long serviceStationId);

     /**
      * @description: 根据一网服务站id查询下属二网服务站
      * @param:
      * @return: 
      * @exception:
      * @author: shiliqiang
      * @company: CTY Technology Co.,Ltd
      * @since: 2022/5/18 9:57
      * @version: V1.0
      */
	List<ServiceStationT> querychildrenStationByFatherStationId(@Param("stationId") Long stationId);

	 /**
	  * @description:根据二网服务站id查询一网服务站
	  * @param:
	  * @return:
	  * @exception:
	  * @author: shiliqiang
	  * @company: CTY Technology Co.,Ltd
	  * @since: 2022/5/18 10:18
	  * @version: V1.0
	  */
	ServiceStationT queryFatherStationByChildrenStationId(@Param("stationId") Long stationId, @Param("businessNo") String businessNo);

	 /**
	  * @description:查询一网对应的二网服务站信息，包括经纬度、距离等
	  * @param: 
	  * @return: 
	  * @exception:
	  * @author: shiliqiang
	  * @company: CTY Technology Co.,Ltd
	  * @since: 2022/5/18 11:44
	  * @version: V1.0
	  */
	 List<Map<String, Object>> querychildrenStationInfo(@Param("ServiceStationId") Long ServiceStationId, @Param("StationName") String StationName, @Param("PositionLon") String PositionLon, @Param("PositionLat") String PositionLat);


	 //获取主动服务的服务站列表
	List<Map<String,Object>> getWhiteList(Map<String,Object> map);

	List<SSRepairT> queryRepairByBusinessNo(@Param("companyList") List<String> companyList,@Param("businessNo") String businessNo);

	List<ServiceStationT> getOutServiceStation(Map<String, Object> map);
	Long getOutServiceStationTotal(Map<String, Object> map);
}